# -*- coding:utf-8 -*-
import time
import pandas as pd

from infrastructure.utils.dateUtil import getTodayyyyymmddInt
from infrastructure.utils.dbUtil import SqliteUtil, SqliteConfig
from infrastructure.utils.requestUtil import get
from config.baseConfiguration import Sqlite3Config
from infrastructure.utils.result import R
from infrastructure.utils.log import logger

config = SqliteConfig(dbPath=Sqlite3Config.dbPath)


def insertOrUpdateGroupInfo(dti):
    db = SqliteUtil.get_instance(dbConfig=config)
    groupSql = "select id, name, info, clsCode, type from group_info"
    r = db.get_all(groupSql)
    unprocessed = {}
    groups = {}
    if r is not None and len(r) > 0:
        for g in r:
            groups[g[1]] = g[0]
            unprocessed[g[3]] = g[0]
    else:
        groups = {}
    maxIdSql = "select max(id) from group_info"

    def do(plateType):
        page = 0
        time.sleep(1)
        url = f"https://x-quote.cls.cn/web_quote/plate/plate_list?app=CailianpressWeb&os=web&page=0&rever=1&sv=7.7.5&token=62s122plv6jx2TF1LQfA3B495A9R92te2859838&type={plateType}&uid=2859838&way=change&sign=7f9945ea8cc1507755eb2cc96229948e"
        while True:
            url = url.replace(f"page={page}", f"page={page + 1}")
            r = get(url, {})
            if r is None:
                time.sleep(2)
                r = get(url, {})
            if r is None:
                print()
            d = r["data"]
            ps = d['plate_data']
            for p in ps:
                code:str = p['secu_code'].upper()
                if code not in unprocessed:
                    continue
                else:
                    unprocessed.pop(code)
                name = p['secu_name'].upper()
                up_num = p['limit_up']
                down_num = p['limit_down']
                rdratio = round(up_num * 1.0 /(up_num + down_num) * 100, 2)
                limit_up_num = p['limit_up_num']
                limit_down_num = p['limit_down_num']
                rise = round(p['change']*100, 2)
                time.sleep(1)
                plateInfoUrl = f"https://x-quote.cls.cn/web_quote/plate/info?app=CailianpressWeb&os=web&secu_code={code.lower()}&sv=7.7.5&token=62s122plv6jx2TF1LQfA3B495A9R92te2859838&uid=2859838&sign=23f60857d2fad34d2d6bf38cc540ba46%E3%80%81"
                pir = get(plateInfoUrl, {})
                if pir is None:
                    time.sleep(2)
                    pir = get(plateInfoUrl, {})
                if pir is None:
                    continue
                # up_num = pir["data"]["up_num"]
                # down_num = pir["data"]['down_num']
                desc = pir["data"]['desc']
                # code = pir["data"]['secu_code']
                # name = pir["data"]['secu_name']
                time.sleep(1)
                stocksUrl = f"https://x-quote.cls.cn/web_quote/plate/stocks?app=CailianpressWeb&os=web&rever=1&secu_code={code.lower()}&sv=7.7.5&token=62s122plv6jx2TF1LQfA3B495A9R92te2859838&uid=2859838&way=last_px&sign=7ffd54a239f2fe206c03231c7e7016f9"
                stocksR = get(stocksUrl, {})
                if stocksR is None:
                    time.sleep(2)
                    stocksR = get(stocksUrl, {})
                if stocksR is None:
                    print()
                stocksStr = "'" + "','".join([s['secu_code'].upper() for s in stocksR["data"]['stocks']]) + "'"

                groupId = 0
                if name in groups:
                    groupId = groups[name]
                    # TODO 可以根据情况开启更新
                    # db.update(f"update group_info set clsCode='{code}', info='{desc}', type='{plateType}' where id={groupId}")
                else:
                    groupId = db.get_one(maxIdSql)[0] + 1
                    db.save(f"insert into group_info(id, name, clsCode, info, type) VALUES ({groupId}, '{name}', '{code}', '{desc}', '{plateType}')")
                mculbc = db.get_one(f"select max(count) from stock_board where type=1 and dti={dti} and code in ({stocksStr})")[0]
                mculbc = mculbc if mculbc is not None else 0
                db.save(f"insert into group_rotation(dti, group_id, rise_conut, down_count, ulbc, dlbc, mculbc, rise, rdratio) VALUES ({dti}, {groupId}, {up_num}, {down_num}, {limit_up_num}, {limit_down_num}, {mculbc}, {rise}, {rdratio})")
            if d["is_all"] == 1:
                break
            else:
                page = page + 1

    do("industry")# 行业板块 industry
    do("concept")# 概念 concept
    do("area")# 地域 area

    if len(unprocessed) > 0:
        for c in unprocessed:
            time.sleep(1)
            plateUrl = (f"https://x-quote.cls.cn/web_quote/plate/info?app=CailianpressWeb&os=web&secu_code={c.lower()}&sv"
                        "=7.7.5&token=62s122plv6jx2TF1LQfA3B495A9R92te2859838&uid=2859838&sign"
                        "=23f60857d2fad34d2d6bf38cc540ba46")
            plateR = get(plateUrl, {})
            if plateR is None:
                time.sleep(2)
                plateR = get(plateUrl, {})
            if plateR is None:
                print()
            plate = plateR['data']
            up_num = plate['up_num']
            down_num = plate['down_num']
            rdratio = round(up_num * 1.0 / (up_num + down_num) * 100, 2)
            time.sleep(1)
            stockUrl = f"https://x-quote.cls.cn/web_quote/plate/stocks?app=CailianpressWeb&os=web&rever=1&secu_code={c.lower()}&sv=7.7.5&token=62s122plv6jx2TF1LQfA3B495A9R92te2859838&uid=2859838&way=last_px&sign=7ffd54a239f2fe206c03231c7e7016f9"
            stocksR = get(stockUrl, {})
            if stocksR is None:
                time.sleep(2)
                stocksR = get(stockUrl, {})
            if stocksR is None:
                print()
            stocksStr = "'" + "','".join([s['secu_code'].upper() for s in stocksR["data"]['stocks']]) + "'"
            mculbc = db.get_one(f"select max(count) from stock_board where type=1 and dti={dti} and code in ({stocksStr})")[0]
            mculbc = mculbc if mculbc is not None else 0
            db.save(f"insert into group_rotation(dti, group_id, rise_conut, down_count, ulbc, dlbc, mculbc, rise, rdratio) VALUES ({dti}, {unprocessed[c]}, {up_num}, {down_num}, {plate['limit_up_num']}, {plate['limit_down_num']}, {mculbc}, {round(plate['change']*100, 2)}, {rdratio})")


def updateGroupRotationBoard(dti):
    db = SqliteUtil.get_instance(dbConfig=config)
    bsql = f"""
        select l.code, l.group_id, l.dti, b.count, b.type
        from stock_board_logic l
            left join stock_board b on b.dti=l.dti and b.code=l.code
            left join group_info i on l.group_id = i.id
        where l.group_id is not NULL
          and l.dti={dti}
    """
    brs = db.get_all(bsql)
    if brs is not None and len(brs) > 0:
        gcDict = {}
        for r in brs:
            group_id = r[1]
            count = r[3]
            type = r[4]
            if group_id not in gcDict:
                gcDict[group_id] = {
                    'sbs': 0,
                    'lb2s': 0,
                    'lb3s': 0,
                    'lb4ps': 0,
                    'dlbc': 0,
                }
            if type == 0:
                gcDict[group_id]['dlbc'] = gcDict[group_id]['dlbc'] + 1
            elif count == 1:
                gcDict[group_id]['sbs'] = gcDict[group_id]['sbs'] + 1
            elif count == 2:
                gcDict[group_id]['lb2s'] = gcDict[group_id]['lb2s'] + 1
            elif count == 3:
                gcDict[group_id]['lb3s'] = gcDict[group_id]['lb3s'] + 1
            else:
                gcDict[group_id]['lb4ps'] = gcDict[group_id]['lb4ps'] + 1
        for gi in gcDict.keys():
            sbs = gcDict[gi]['sbs']
            lb2s = gcDict[gi]['lb2s']
            lb3s = gcDict[gi]['lb3s']
            lb4ps = gcDict[gi]['lb4ps']
            dlbc = gcDict[gi]['dlbc']
            ulbc = sbs + lb2s + lb3s + lb4ps
            usql = f"update group_rotation set ulbc={ulbc}, sbs={sbs}, lb2s={lb2s}, lb3s={lb3s}, lb4ps={lb4ps}, dlbc={dlbc} where dti={dti} and group_id={gi}"
            db.update(usql)
            if ulbc >= 5:
                grisesql = f"select sdti from group_rise where group_id={gi} and edti is null limit 1"
                griseR = db.get_one(grisesql)
                if griseR is None or len(griseR) == 0:
                    griseIsql = f"insert into group_rise(group_id, sdti) values ({gi}, {dti})"
                    db.save(griseIsql)


def updateGroupRise(dti):
    logger.info(f"开始更新{dti}的股票板块崛起信息")
    db = SqliteUtil.get_instance(dbConfig=config)
    grisesql = f"select group_id, sdti from group_rise where edti is null"
    griseR = db.get_all(grisesql)
    if griseR is not None and len(griseR) > 0:
        for r in griseR:
            group_id = r[0]
            sdti = r[1]
            groSql = f"select sbs, lb2s, lb3s, lb4ps from group_rotation where group_id={group_id} and dti={dti} limit 1"
            groR = db.get_one(groSql)
            if groR is not None and len(groR) > 0:
                sbs = groR[0]
                lb2s = groR[1]
                lb3s = groR[2]
                lb4ps = groR[3]
                bzs = sbs + lb2s + lb3s + lb4ps
                if bzs == 0:
                    groUSql = f"update group_rise set edti={dti} where group_id={group_id} and sdti={sdti}"
                    db.update(groUSql)
    logger.info(f"完成更新{dti}的股票板块崛起信息")


def insertHistoryGroupRise(dti):
    db = SqliteUtil.get_instance(dbConfig=config)
    groSql = f"select group_id, sbs, lb2s, lb3s, lb4ps from group_rotation where rise_conut >= 5 and dti={dti}"
    groR = db.get_all(groSql)
    if groR is not None and len(groR) > 0:
        for r in groR:
            group_id = r[0]
            sbs = r[1]
            lb2s = r[2]
            lb3s = r[3]
            lb4ps = r[4]
            bzs = sbs + lb2s + lb3s + lb4ps
            if bzs >= 5:
                grisesql = f"select sdti from group_rise where group_id={group_id} and edti is null"
                griseR = db.get_one(grisesql)
                if griseR is None or len(griseR) == 0:
                    griseIsql = f"insert into group_rise(group_id, sdti) values ({group_id}, {dti})"
                    db.save(griseIsql)


def groupTopStatistics(dti):
    logger.info(f"开始更新{dti}的股票板块前排名次信息")
    db = SqliteUtil.get_instance(dbConfig=config)
    ubTopSql = f"""
        select group_id
        from group_rotation 
        where dti={dti} and ulbc >= 5
    """
    ubTopR = db.get_all(ubTopSql)
    if ubTopR is not None and len(ubTopR) > 0:
        ubTopUsql = f"update group_rotation set ubtop=1 where group_id in ({','.join([str(r[0]) for r in ubTopR])}) and dti={dti}"
        db.update(ubTopUsql)

    giSql = f"select id from group_info"
    giR = db.get_all(giSql)
    if giR is not None and len(giR) > 0:
        for r in giR:
            group_id = r[0]
            t5Sql = f"select ubtop from group_rotation where dti <= {dti} and group_id={group_id} order by dti desc limit 5"
            t5R = db.get_all(t5Sql)

            t10Sql = f"select ubtop from group_rotation where dti <= {dti} and group_id={group_id} order by dti desc limit 10"
            t10R = db.get_all(t10Sql)

            t20Sql = f"select ubtop from group_rotation where dti <= {dti} and group_id={group_id} order by dti desc limit 20"
            t20R = db.get_all(t20Sql)

            t30Sql = f"select ubtop from group_rotation where dti <= {dti} and group_id={group_id} order by dti desc limit 30"
            t30R = db.get_all(t30Sql)

            t60Sql = f"select ubtop from group_rotation where dti <= {dti} and group_id={group_id} order by dti desc limit 60"
            t60R = db.get_all(t60Sql)

            t120Sql = f"select ubtop from group_rotation where dti <= {dti} and group_id={group_id} order by dti desc limit 120"
            t120R = db.get_all(t120Sql)

            t240Sql = f"select ubtop from group_rotation where dti <= {dti} and group_id={group_id} order by dti desc limit 240"
            t240R = db.get_all(t240Sql)

            t480Sql = f"select ubtop from group_rotation where dti <= {dti} and group_id={group_id} order by dti desc limit 480"
            t480R = db.get_all(t480Sql)

            ub5d = sum([1 if d[0] is not None and d[0] == 1 else 0 for d in t5R]) if t5R is not None and len(t5R) > 0 else 0
            ub10d = sum([1 if d[0] is not None and d[0] == 1 else 0 for d in t10R]) if t10R is not None and len(t10R) > 0 else 0
            ub20d = sum([1 if d[0] is not None and d[0] == 1 else 0 for d in t20R]) if t20R is not None and len(t20R) > 0 else 0
            ub30d = sum([1 if d[0] is not None and d[0] == 1 else 0 for d in t30R]) if t30R is not None and len(t30R) > 0 else 0
            ub60d = sum([1 if d[0] is not None and d[0] == 1 else 0 for d in t60R]) if t60R is not None and len(t60R) > 0 else 0
            ub120d = sum([1 if d[0] is not None and d[0] == 1 else 0 for d in t120R]) if t120R is not None and len(t120R) > 0 else 0
            ub240d = sum([1 if d[0] is not None and d[0] == 1 else 0 for d in t240R]) if t240R is not None and len(t240R) > 0 else 0
            ub480d = sum([1 if d[0] is not None and d[0] == 1 else 0 for d in t480R]) if t480R is not None and len(t480R) > 0 else 0

            usql = f"""
                update group_rotation 
                set ub5d={ub5d}, ub10d={ub10d}, ub20d={ub20d}, ub20d={ub20d}, ub30d={ub30d}, ub60d={ub60d}, ub120d={ub120d}, ub240d={ub240d}, ub480d={ub480d}
                where group_id={group_id} and dti={dti}
            """
            db.update(usql)
    logger.info(f"完成更新{dti}的股票板块前排名次信息")


def deleteGroupRotation(dti: int = None):
    if dti is None:
        dti = getTodayyyyymmddInt()
    db = SqliteUtil.get_instance(dbConfig=config)
    db.delete(f"delete from group_rotation where dti={dti}")
    db.delete(f"delete from group_rise where sdti={dti}")
    logger.info(f"删除{dti}的板块轮动信息成功")

def updateGroupRotation(dti: int = None) -> R:
    if dti is None:
        dti = getTodayyyyymmddInt()
    logger.info(f"开始更新{dti}的股票板块汇总信息")
    db = SqliteUtil.get_instance(dbConfig=config)
    bcSql = f"select count(code) from stock_board where dti={dti}"
    bcR = db.get_one(bcSql)
    if bcR is None or len(bcR) == 0:
        return R.error("没有查询到指定日期得封板信息")

    glSql = f"select id from group_info"
    glR = db.get_all(glSql)
    if glR is None or len(glR) == 0:
        return R.error("没有查询到股票分组信息")
    grs = []
    for r in glR:
        group_id = r[0]
        bgSql = f"select bl.group_id, b.code, b.type, b.count from stock_board b left join stock_board_logic bl on b.code=bl.code and b.dti=bl.dti where b.dti={dti} and bl.type=0 and bl.group_id={group_id}"
        bgR = db.get_all(bgSql)
        if bgR is None or len(bgR) == 0:
            grs.append(str((group_id, dti, 0, 0, 0, 0, 0, 0, 0)))
            continue
        bgDf = pd.DataFrame(bgR, columns=['group_id', 'code', 'type', 'count'])
        ulbc = bgDf[bgDf.type == 1].shape[0]
        if ulbc >= 5:
            grrSql = f"select * from group_rise where group_id={group_id} and edti is null"
            grrR = db.get_all(grrSql)
            if grrR is None or len(grrR) == 0:
                db.save(f"insert into group_rise (group_id, sdti) values ({group_id}, {dti})")
        dlbc = bgDf[bgDf.type == 0].shape[0]
        mculbc = bgDf[(bgDf['count'] > 1) & (bgDf['type'] == 1)].shape[0]
        sbs = bgDf[(bgDf['count'] == 1) & (bgDf['type'] == 1)].shape[0]
        lb2s = bgDf[(bgDf['count'] == 2) & (bgDf['type'] == 1)].shape[0]
        lb3s = bgDf[(bgDf['count'] == 3) & (bgDf['type'] == 1)].shape[0]
        lb4ps = bgDf[(bgDf['count'] > 3) & (bgDf['type'] == 1)].shape[0]
        grs.append(str((group_id, dti, ulbc, dlbc, mculbc, sbs, lb2s, lb3s, lb4ps)))
    db.save(f"insert into group_rotation (group_id, dti, ulbc, dlbc, mculbc, sbs, lb2s, lb3s, lb4ps) values {','.join(grs)}")
    logger.info(f"完成更新{dti}的股票板块汇总信息")



def groupRotation(dti: int = None):
    if dti is None:
        dti = getTodayyyyymmddInt()
    updateGroupRotation(dti)
    updateGroupRise(dti)
    groupTopStatistics(dti)